package com.syp.jrx.batch.reader;

import com.syp.jrx.batch.entity.entity;
import com.syp.jrx.batch.entity.entityRowMapper;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.util.ArrayList;


@Service
@Configuration
public class ReaderService {
    @Autowired
    //@Qualifier("DataSource")
    private DataSource dataSource;

    @Bean
    public JdbcCursorItemReader<ArrayList<entity>> readerDatabase() {
        JdbcCursorItemReader<ArrayList<entity>> jdbcReader = new JdbcCursorItemReader<>();
        //设置数据源
        jdbcReader.setDataSource( dataSource );
        //设置SQL
        jdbcReader.setSql("SELECT t.trans_id,t.cust_id,c.surname,t.bill,t.trans_type,t.txn_datetime " +
                "FROM  transaction as t LEFT JOIN customer as c on t.cust_id=c.cust_id " +
                "WHERE TO_DAYS(Now())-TO_DAYS(t.txn_datetime) > 1 " +
                "GROUP by c.cust_id ,t.trans_id");
        //设置参数
        //jdbcReader.setPreparedStatementSetter();
        //设置POJO映射类
        jdbcReader.setRowMapper(new entityRowMapper());
        return jdbcReader;
    }
    /*
    public MySqlPagingQueryProvider queryProvider() {
        MySqlPagingQueryProvider queryProvider = new MySqlPagingQueryProvider();
        queryProvider.setSelectClause("SELECT t.trans_id,t.cust_id,c.surname,t.bill,t.trans_type,t.txn_datetime");
        queryProvider.setFromClause("FROM  transaction as t LEFT JOIN customer as c on t.cust_id=c.cust_id");
        queryProvider.setWhereClause("WHERE TO_DAYS(Now())-TO_DAYS(t.txn_datetime) >1 ");
        queryProvider.setGroupClause("GROUP by c.cust_id ,t.trans_id");
        //queryProvider.setGroupClause("order by t.cust_id ");
        Map<String, Order> sortKeys = new HashMap<>();
        sortKeys.put("cust_id", Order.ASCENDING);
        queryProvider.setSortKeys(sortKeys);
        return queryProvider;
    }*/
}
